#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import tkinter as tk
from tkinter import ttk, filedialog, scrolledtext
import mysql.connector
from mysql.connector import Error
import threading
from queue import Queue

class MySQLGUI:
    def __init__(self, root):
        self.root = root
        self.root.title("MySQL脚本执行工具")
        self.root.geometry("1000x640")
        
        # 数据库连接信息
        # 初始化配置
        import os
        import configparser
        self.config = configparser.ConfigParser()
        
        # 默认值
        self.db_name = tk.StringVar(value="station_gantry")
        self.db_user = tk.StringVar(value="justdoit")
        self.db_password = tk.StringVar(value="iloveyou")
        self.script_path = tk.StringVar()
        
        # 如果配置文件存在则加载
        if os.path.exists('config.ini'):
            self.config.read('config.ini')
            if 'DATABASE' in self.config:
                self.db_name.set(self.config['DATABASE'].get('db_name', 'station_gantry'))
                self.db_user.set(self.config['DATABASE'].get('db_user', 'root'))
                self.db_password.set(self.config['DATABASE'].get('db_password', '0000'))
                self.script_path.set(self.config['DATABASE'].get('script_path', ''))
        
        # 创建顶部控件
        self.create_top_controls()
        
        # 创建脚本选择和执行控件
        self.create_script_controls()
        
        # 创建服务器列表和状态表格
        self.create_server_list()
        
        # 线程队列
        self.queue = Queue()



    def show_config_window(self):
        """显示配置窗口"""
        self.config_window = tk.Toplevel(self.root)
        self.config_window.title("系统配置")
        self.config_window.geometry("400x300")

        # 数据库配置
        ttk.Label(self.config_window, text="数据库名称").grid(row=0, column=0, padx=5, pady=5)
        self.config_db_name = ttk.Entry(self.config_window)
        self.config_db_name.grid(row=0, column=1, padx=5, pady=5)
        self.config_db_name.insert(0, self.db_name.get())

        ttk.Label(self.config_window, text="用户名").grid(row=1, column=0, padx=5, pady=5)
        self.config_db_user = ttk.Entry(self.config_window)
        self.config_db_user.grid(row=1, column=1, padx=5, pady=5)
        self.config_db_user.insert(0, self.db_user.get())

        ttk.Label(self.config_window, text="密码").grid(row=2, column=0, padx=5, pady=5)
        self.config_db_password = ttk.Entry(self.config_window, show="*")
        self.config_db_password.grid(row=2, column=1, padx=5, pady=5)
        self.config_db_password.insert(0, self.db_password.get())

        # 脚本路径配置
        ttk.Label(self.config_window, text="脚本路径").grid(row=3, column=0, padx=5, pady=5)
        self.config_script_path = ttk.Entry(self.config_window)
        self.config_script_path.grid(row=3, column=1, padx=5, pady=5)
        self.config_script_path.insert(0, self.script_path.get())

        # 保存按钮
        ttk.Button(self.config_window, text="保存配置", command=self.save_config).grid(row=4, column=0, columnspan=2, pady=10)



    def save_config(self):
        """保存配置到config.ini文件"""
        import configparser
        config = configparser.ConfigParser()
        config['DATABASE'] = {
            'db_name': self.config_db_name.get(),
            'db_user': self.config_db_user.get(),
            'db_password': self.config_db_password.get(),
            'script_path': self.config_script_path.get()
        }

        with open('config.ini', 'w') as configfile:
            config.write(configfile)

        # 更新主界面配置
        self.db_name.set(self.config_db_name.get())
        self.db_user.set(self.config_db_user.get())
        self.db_password.set(self.config_db_password.get())
        self.script_path.set(self.config_script_path.get())

        self.config_window.destroy()

    def create_top_controls(self):
        """创建顶部数据库连接信息控件"""
        top_frame = ttk.Frame(self.root, padding=10)
        top_frame.grid(row=0, column=0, sticky="ew", columnspan=3)
        
        labels = ["数据库名称", "数据库用户", "登录密码"]
        variables = [self.db_name, self.db_user, self.db_password]
        
        for i, (label_text, var) in enumerate(zip(labels, variables)):
            ttk.Label(top_frame, text=label_text).grid(row=0, column=i*2, padx=5, pady=5)
            ttk.Entry(top_frame, textvariable=var).grid(row=0, column=i*2+1, padx=5, pady=5, sticky="ew")
            top_frame.columnconfigure(i*2+1, weight=1)
            
            if label_text == "登录密码":
                ttk.Button(top_frame, text="测试连接", command=self.test_connection).grid(row=0, column=i*2+2, padx=5, pady=5)
                self.config_btn = ttk.Button(top_frame, text="系统配置", command=self.show_config_window)
                self.config_btn.grid(row=0, column=i*2+3, padx=5, pady=5)


    def test_connection(self):
        """并行测试数据库连接"""
        # 从服务器列表富文本框中获取服务器列表
        servers = self.server_list.get("1.0", tk.END).strip().splitlines()
        if not servers:
            return

        # 清空表格
        for item in self.tree.get_children():
            self.tree.delete(item)
        
        # 获取数据库连接信息
        db_name = self.db_name.get()
        db_user = self.db_user.get()
        db_password = self.db_password.get()

        
        # 为每个服务器启动一个线程进行连接测试
        for server in servers:
            if server.strip():
                thread = threading.Thread(target=self.run_connection_test, args=(server.strip(), db_name, db_user, db_password))
                thread.start()

    def run_connection_test(self, host, db_name, db_user, db_password):
        """在后台线程中测试连接"""
        try:
            connection = mysql.connector.connect(
                host=host,
                database=db_name,
                user=db_user,
                password=db_password
            )
            connection.close()
            # 修改这里：将host和结果一起放入队列
            self.queue.put(("success", host, "连接成功"))
        except Exception as e:
            # 修改这里：将host和错误信息一起放入队列
            self.queue.put(("error", host, str(e)))

        self.root.after(100, self.process_queue)

    def process_queue(self, host):
        """处理线程队列中的消息"""
        while not self.queue.empty():
            item = self.queue.get()
            if item[0] == "success":
                self.tree.insert("", "end", values=(host, "成功"))
            elif item[0] == "error":
                # 修改这里：直接使用item[1]获取错误消息
                error_msg = item[1] if len(item) > 1 else "未知错误"
                self.tree.insert("", "end", values=(host, f"失败: {error_msg}"))

        self.root.after(100, lambda: self.process_queue(host))

    def create_script_controls(self):
        """创建脚本选择和执行控件"""
        script_frame = ttk.Frame(self.root, padding=10)
        script_frame.grid(row=1, column=0, sticky="ew", columnspan=3)
        
        ttk.Label(script_frame, text="SQL脚本文件").grid(row=0, column=0, padx=5, pady=5)
        self.script_path = tk.StringVar()
        ttk.Entry(script_frame, textvariable=self.script_path, width=50).grid(row=0, column=1, padx=5, pady=5, sticky="ew")
        script_frame.columnconfigure(1, weight=1)
        ttk.Button(script_frame, text="浏览", command=self.browse_script).grid(row=0, column=2, padx=5, pady=5)
        ttk.Button(script_frame, text="执行脚本", command=self.execute_script).grid(row=0, column=3, padx=5, pady=5)
        
    def create_server_list(self):
        """创建服务器列表和状态表格"""
        # 服务器列表富文本框
        server_frame = ttk.Frame(self.root, padding=10)
        server_frame.grid(row=2, column=0, sticky="nsew")
        
        ttk.Label(server_frame, text="MySQL服务器列表").pack(pady=5)
        self.server_list = scrolledtext.ScrolledText(server_frame, width=30, height=20)
        self.server_list.pack(expand=True, fill="both")
        
        # 状态表格
        status_frame = ttk.Frame(self.root, padding=10)
        status_frame.grid(row=2, column=1, columnspan=2, sticky="nsew")
        
        columns = ("服务器", "连接状态", "执行状态", "执行脚本")
        self.tree = ttk.Treeview(status_frame, columns=columns, show="headings")
        self.tree.heading("服务器", text="服务器")
        self.tree.heading("连接状态", text="连接状态")
        self.tree.heading("执行状态", text="执行状态")
        self.tree.heading("执行脚本", text="执行脚本")
        self.tree.pack(expand=True, fill="both", side=tk.RIGHT)
        
        # 添加选择事件绑定
        self.tree.bind("<ButtonRelease-1>", self.on_tree_select)
        self.tree.bind("<Control-c>", self.copy_selected_text)
        
        # 调整列宽
        self.root.columnconfigure(0, weight=1)
        self.root.columnconfigure(1, weight=2)
        self.root.rowconfigure(2, weight=1)
        
    def browse_script(self):
        """选择SQL脚本文件"""
        file_path = filedialog.askopenfilename(filetypes=[("SQL文件", "*.sql")])
        if file_path:
            self.script_path.set(file_path)
        
    def execute_script(self):
        """执行SQL脚本"""
        script_path = self.script_path.get()
        if not script_path:
            return
        
        # 清空表格
        for item in self.tree.get_children():
            self.tree.delete(item)
        
        # 获取数据库连接信息
        db_name = self.db_name.get()
        db_user = self.db_user.get()
        db_password = self.db_password.get()
        
        # 从服务器列表获取服务器地址
        servers = self.server_list.get("1.0", tk.END).strip().splitlines()
        if not servers:
            return

        # 为每个服务器启动线程执行脚本
        for server in servers:
            if server.strip():
                thread = threading.Thread(target=self.run_script_thread, args=(server.strip(), db_name, db_user, db_password, script_path))
                thread.start()

    def run_script_thread(self, host,db_name, db_user, db_password, script_path):
        try:
            with open(script_path, 'r', encoding='utf-8') as file:
                script = file.read()

            statements = self.split_sql(script)

            connection = mysql.connector.connect(
                host=host,
                database=db_name,
                user=db_user,
                password=db_password
            )
            cursor = connection.cursor(buffered=True)

            success_count = 0
            for statement in statements:
                if not statement.strip():
                    continue
                try:
                    cursor.execute(statement)
                    while cursor.nextset():
                        pass  # 清除所有结果集
                    connection.commit()
                    success_count += 1
                except Error as e:
                    connection.rollback()
                    self.queue.put(("error", host, str(e), statement))
                    break

            if success_count == len(statements):
                self.queue.put(("success", host, "脚本执行成功", ""))

            cursor.close()
            connection.close()
        except Exception as e:
            self.queue.put(("error", host, str(e), script))

        self.root.after(100, lambda: self.process_queue(host))

    def split_sql(self, script):
        statements = []
        current_stmt = ""
        delimiter = ";"

        lines = script.splitlines()
        for line in lines:
            stripped = line.strip()
            if stripped.lower().startswith("delimiter"):
                parts = stripped.split()
                if len(parts) > 1:
                    delimiter = parts[1]
                continue
            current_stmt += line + "\n"
            if current_stmt.strip().endswith(delimiter):
                statements.append(current_stmt.rstrip(delimiter).strip())
                current_stmt = ""
                delimiter = ";"  # 重置为默认
        if current_stmt.strip():
            statements.append(current_stmt)
        return statements
        
    def on_tree_select(self, event):
        """处理表格选择事件"""
        item = self.tree.identify_row(event.y)
        column = self.tree.identify_column(event.x)
        if item and column:
            self.tree.selection_set(item)

    def copy_selected_text(self, event):
        """复制选中单元格的文本"""
        selected = self.tree.selection()
        if selected:
            item = selected[0]
            column = self.tree.identify_column(event.x)
            if item and column:
                cell_value = self.tree.item(item, "values")[int(column[1:])-1]
                self.root.clipboard_clear()
                self.root.clipboard_append(cell_value)

    def process_queue(self, host=None):
        """处理线程队列中的消息"""
        while not self.queue.empty():
            item = self.queue.get()
            host = item[1] if len(item) > 1 else "未知主机"
            if item[0] == "success":
                if len(item) == 2:
                    self.tree.insert("", "end", values=(host, "成功", "", ""))
                else:
                    self.tree.insert("", "end", values=(host, "成功", "脚本执行成功", ""))
            elif item[0] == "error":
                error_msg = item[2] if len(item) > 2 else "未知错误"
                script = item[3] if len(item) > 3 else ""
                self.tree.insert("", "end", values=(host, "失败", f"失败: {error_msg}", script))
        
        self.root.after(100, self.process_queue)

if __name__ == "__main__":
    root = tk.Tk()
    app = MySQLGUI(root)
    root.mainloop()